package com.oa.dao.impl;

import com.oa.dao.BaseDao;
import com.oa.dao.EmployeeDao;
import com.oa.entity.Attendance;
import com.oa.entity.Employee;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Description TODO
 * @Author hyl
 * @Date 2022/7/5 9:20
 **/
public class EmployeeDaoImpl extends BaseDao implements EmployeeDao {
	@Override
	public Employee findByParam(String empNo, String pwd) {
		//2、创建连接对象
		Connection conn = getConnection();
		//3、编写sql
		String sql = "Select e.*,p.posName from employee e LEFT JOIN  position  p ON e.positionid =p.id " +
				" where employeeNo=? and `password`=? ";
		//4、创建执行对象
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, empNo);
			pstm.setString(2, pwd);
			//5、执行
			rs = pstm.executeQuery(); //虚拟表==》查询结果的表
			//6、处理执行结果
			Employee tmp = null;
			while(rs.next()){
				tmp = new Employee( rs.getInt("id"),
						rs.getString("employeeNo"),
						rs.getString("password"),
						rs.getString("name"),
						rs.getString("email"),
						rs.getInt("deptId"),
						rs.getString("entryTime"),
						rs.getInt("positionId"),
						rs.getString("photo"));
				tmp.setPositionName(rs.getString("posName"));
			}
			return tmp;
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		}finally {
			//7、关闭
			closeAll(rs,pstm,conn);
		}
		return null;
	}



	@Override
	public List<Employee> findParam(String empName) {
		//2、创建连接对象
		Connection conn = getConnection();
		//3、编写sql
		String sql = "Select e.*,p.posName from employee e LEFT JOIN  position  p ON e.positionid =p.id " +
				" where name like ? ";
		//4、创建执行对象
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			pstm = conn.prepareStatement(sql);
			if(empName==null){
				empName="";
			}
			pstm.setString(1, "%"+empName+"%");
			//5、执行
			rs = pstm.executeQuery(); //虚拟表==》查询结果的表
			//6、处理执行结果
			List<Employee> list = new ArrayList<Employee>();
			while(rs.next()){
				Employee tmp = new Employee( rs.getInt("id"),
						rs.getString("employeeNo"),
						rs.getString("password"),
						rs.getString("name"),
						rs.getString("email"),
						rs.getInt("deptId"),
						rs.getString("entryTime"),
						rs.getInt("positionId"),
						rs.getString("photo"));
				tmp.setPositionName("posName");
				list.add(tmp);
			}
			return list;
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		}finally {
			//7、关闭
			closeAll(rs,pstm,conn);
		}
		return null;
	}



	@Override
	public Employee findById(Integer id) {
		//2、创建连接对象
		Connection conn = getConnection();
		//3、编写sql
		String sql = "select * from Employee where id=? ";
		//4、创建执行对象
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			pstm = conn.prepareStatement(sql);
			pstm.setInt(1, id);
			//5、执行
			rs = pstm.executeQuery(); //虚拟表==》查询结果的表
			//6、处理执行结果
			Employee tmp = null;
			while(rs.next()){
				tmp = new Employee( rs.getInt("id"),
						rs.getString("employeeNo"),
						rs.getString("password"),
						rs.getString("name"),
						rs.getString("email"),
						rs.getInt("deptId"),
						rs.getString("entryTime"),
						rs.getInt("positionId"),
						rs.getString("photo"));
			}
			return tmp;
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		}finally {
			//7、关闭
			closeAll(rs,pstm,conn);
		}
		return null;
	}

	@Override
	public int updatePwd(String newPwd, Integer id) {
		//2、创建连接对象
		Connection conn = getConnection();
		//3、编写sql
		String sql = "update Employee set  password=? where id=? ";
		//4、创建执行对象
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, newPwd);
			pstm.setInt(2, id);
			//5、执行
			int ret = pstm.executeUpdate(); //执行sql之前，对？赋值
			//6、处理执行结果
			return ret;
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		}finally {
			//7、关闭
			closeAll(rs,pstm,conn);
		}
		return 0;
	}

	@Override
	public int add(Employee employee) {
		//2、创建连接对象
		Connection conn = getConnection();
		//3、编写sql
		String sql = "Insert into Employee( id,  employeeNo,  password,  name,  email,  deptId,  entryTime,  positionId,  photo) " +
				" values( ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?)";
		//4、创建执行对象
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			pstm = conn.prepareStatement(sql);
			pstm.setObject(1, employee.getId());
			pstm.setObject(2, employee.getEmployeeNo());
			pstm.setObject(3, employee.getPassword());
			pstm.setObject(4, employee.getName());
			pstm.setObject(5, employee.getEmail());
			pstm.setObject(6, employee.getDeptId());
			pstm.setObject(7, employee.getEntryTime());
			pstm.setObject(8,employee.getPositionId());
			pstm.setObject(9,employee.getPhoto() );
			//5、执行
			int ret = pstm.executeUpdate();
			//6、处理执行结果
			return ret;
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		}finally {
			//7、关闭
			closeAll(rs,pstm,conn);
		}
		return 0;
	}

	@Override
	public int modify(Employee employee) {
		//2、创建连接对象
		Connection conn = getConnection();
		//3、编写sql
		String sql = "update Employee set  " +
				"employeeNo=?,  password=?,  name=?,  email=?,  deptId=?,  entryTime=?,  positionId=?,  photo=?" +
				" where id=? ";
		//4、创建执行对象
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			pstm = conn.prepareStatement(sql);
			pstm.setObject(1, employee.getEmployeeNo());
			pstm.setObject(2, employee.getPassword());
			pstm.setObject(3, employee.getName());
			pstm.setObject(4, employee.getEmail());
			pstm.setObject(5, employee.getDeptId());
			pstm.setObject(6, employee.getEntryTime());
			pstm.setObject(7,employee.getPositionId());
			pstm.setObject(8,employee.getPhoto() );
			pstm.setObject(9, employee.getId());
			//5、执行
			int ret = pstm.executeUpdate();
			//6、处理执行结果
			return ret;
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		}finally {
			//7、关闭
			closeAll(rs,pstm,conn);
		}
		return 0;
	}
}
